package cn.com.qing.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import cn.com.qing.mydb.dbhelp.help.DBConnection;
import cn.com.qing.mydb.dbhelp.reflect.MapReflect;

  
/** 
 * 从excel读取数据/往excel中写入 excel有表头，表头每列的内容对应实体类的属性 
 *  
 * @author nagsh 
 *  
 */  
public class ExcelManage {  

    public static void main(String[] args) throws IOException {  
        String title[] = {"数据库表","字段","类型","默认值","说明"};  
        getExcel(title);
    }  
    
    @SuppressWarnings({ "resource", "deprecation" })
    public static void writer(String path, String fileName,
    		String fileType,List<Map<Object,Object>> list,
    		String titleRow[],List<Integer> columnWidth) throws IOException {  
        Workbook wb = null; 
        String excelPath = path+File.separator+fileName+"."+fileType;
        File file = new File(excelPath);
        Sheet sheet =null;
        //创建工作文档对象   
        if (!file.exists()) {
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook();
            } else if(fileType.equals("xlsx")) {
                wb = new XSSFWorkbook();
            } else {
                return;
            }
            //创建sheet对象   
            sheet = (Sheet) wb.createSheet("sheet1");  
            OutputStream outputStream = new FileOutputStream(excelPath);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
            
        } else {
            if (fileType.equals("xls")) {  
                wb = new HSSFWorkbook();  
            } else if(fileType.equals("xlsx")) { 
                wb = new XSSFWorkbook();  
                
            } else {  
            	return;
            }  
        }
         //创建sheet对象   
        if (sheet==null) {
            sheet = (Sheet) wb.createSheet("sheet1");  
        }
        
        //添加表头  
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        row.setHeight((short) 540); 
        cell.setCellValue(fileName);    //创建第一行    
        
        CellStyle style = wb.createCellStyle(); // 样式对象      
        // 设置单元格的背景颜色为淡蓝色  
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index); 
        
//        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直      
//        style.setAlignment(CellStyle.ALIGN_CENTER);// 水平   
        style.setWrapText(true);// 指定当单元格内容显示不下时自动换行
       
        cell.setCellStyle(style); // 样式，居中
        
        Font font = wb.createFont();  
//        font.setBoldweight(Font.BOLDWEIGHT_BOLD); 
        font.setFontName("宋体");  
        font.setFontHeight((short) 280);  
        style.setFont(font);  
        // 单元格合并      
        // 四个参数分别是：起始行，起始列，结束行，结束列      
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,titleRow.length-1));  
        sheet.autoSizeColumn(5200);
        
//        row = sheet.createRow(1);    //创建第二行    
        for(int i = 0;i < titleRow.length;i++){  
            cell = row.createCell(i);  
            cell.setCellValue(titleRow[i]);  
            cell.setCellStyle(style); // 样式，居中
            sheet.setColumnWidth(i,columnWidth.get(i));
        }  
//        row.setHeight((short) 540); 
        
        int startrow = 0;
    	int endrow = 0;
    	
    	boolean sw = false;

        //循环写入行数据   
        for (int i = 0; i < list.size(); i++) {  
            row = (Row) sheet.createRow(i+1);  
            row.setHeight((short) 500); 
            Map<Object,Object> map = list.get(i);
            if(map.get(titleRow[0])!=null&&!"".equals(map.get(titleRow[0]))
            	&&(map.get(titleRow[1])==null||"".equals(map.get(titleRow[1])))) {
            	startrow = i+1;
            }
            if((map.get(titleRow[0])==null||"".equals(map.get(titleRow[0])))
            	&&(map.get(titleRow[1])==null||"".equals(map.get(titleRow[1])))) {
            	endrow = i;
            	sw = true;
            }
            for(int j=0;j<titleRow.length;j++) {
            	String value = (String)map.get(titleRow[j]);
            	value = "b'1'".equals(value)?"true":value;
            	value = "b'0'".equals(value)?"false":value;
            	Cell c = row.createCell(j);
            	c.setCellValue(value);
            }
            if(sw) {
            	 sheet.addMergedRegion(new CellRangeAddress(startrow,endrow,0,0));  
            	 sw = false;
            }
        }  
        
        //创建文件流   
        OutputStream stream = new FileOutputStream(excelPath);  
        //写入数据   
        wb.write(stream);  
        //关闭文件流   
        stream.close();  
    }  
    
    public static List<Map<Object, Object>> getMap(String sql){
		try{
            LinkedHashMap<String, Object> properties = MyResource.getJarYaml("application.yml");
            if(properties==null) {
            	properties = MyResource.getSrcYaml("application.yml");
            }
    		Connection connection = DBConnection.getConnect(properties);
    		Statement s = connection.createStatement();
    		long timestart = System.currentTimeMillis();
    		ResultSet rs = s.executeQuery(sql);
    		ResultSetMetaData rsmd = rs.getMetaData();
    		int rows = rsmd.getColumnCount();
    		List<Map<Object, Object>> rsMap = new ArrayList<Map<Object, Object>>();
    		while (rs.next()) {
    			Map<Object, Object> fieldValue = new HashMap<Object, Object>();
    			for (int i = 1; i <= rows; i++) {
    				String fieldClassName = rsmd.getColumnClassName(i);
    				String fieldName = rsmd.getColumnName(i);
    				MapReflect.encasementMapping(fieldClassName, fieldName, rs, fieldValue);
    			}
    			rsMap.add(fieldValue);
    		}
    		rs.close();
    		s.close();
    		connection.close();
    		System.out.println("获取数据花费时间："+(System.currentTimeMillis()-timestart));
    		return rsMap;
    	}catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
    
    @SuppressWarnings("unchecked")
	public static void getExcel(String title[]) throws IOException {
    	LinkedHashMap<String, Object> properties = MyResource.getJarYaml("application.yml");
        if(properties==null) {
        	properties = MyResource.getSrcYaml("application.yml");
        }
    	String tmp = " SELECT " + 
				"	TABLE_NAME " + 
				"FROM " + 
				"	information_schema.`TABLES` " + 
				"WHERE " + 
				"	TABLE_SCHEMA = '@@DATABASE@@'";
		
		String temp = " SELECT " + 
				"	'数据库表' AS '数据库表', " + 
				"	'字段' AS '字段', " + 
				"	'类型' AS '类型', " + 
				"	'默认值' AS '默认值', " + 
				"	'说明' AS '说明' " + 
				" UNION ALL " + 
				"	SELECT " + 
				"		'@@TABLENAME@@' AS '数据库表', " + 
				"		'' AS '字段', " + 
				"		'' AS '类型', " + 
				"		'' AS '默认值', " + 
				"		( " + 
				"			SELECT " + 
				"				TABLE_COMMENT " + 
				"			FROM " + 
				"				information_schema.`TABLES` " + 
				"			WHERE " + 
				"				TABLE_SCHEMA = '@@DATABASE@@' AND TABLE_NAME = '@@TABLENAME@@' " + 
				"		) AS '说明' " + 
				"	UNION ALL " + 
				"		SELECT DISTINCT " + 
				"			'' AS '数据库表', " + 
				"			COLUMN_NAME AS '字段', " + 
				"			COLUMN_TYPE AS '类型', " + 
				"			COLUMN_DEFAULT AS '默认值', " + 
				"			COLUMN_COMMENT AS '说明' " + 
				"		FROM " + 
				"			information_schema. COLUMNS " + 
				"		WHERE " + 
				"			TABLE_SCHEMA = '@@DATABASE@@' " + 
				"		AND TABLE_NAME = '@@TABLENAME@@' " +
				" UNION ALL SELECT " + 
				"	'' AS '数据库表', " + 
				"	'' AS '字段', " + 
				"	'' AS '类型', " + 
				"	'' AS '默认值', " + 
				"	'' AS '说明' " + 
				" ";
		String dataBase = (String)(((LinkedHashMap<String, Object>)properties.get("jdbc")).get("database"));
		tmp = tmp.replace("@@DATABASE@@", dataBase);
		temp = temp.replace("@@DATABASE@@", dataBase);
		List<Map<Object, Object>> tablesMap = getMap(tmp);
		String sql = "";
		int i = 0;
		for(Map<Object, Object> m :tablesMap) {
			if(i==0) {
				sql = sql + "" + temp.replace("@@TABLENAME@@",m.get("TABLE_NAME").toString());
			}else {
				sql = sql + " UNION ALL " + temp.replace("@@TABLENAME@@",m.get("TABLE_NAME").toString());
			}
			i++;
		}
		
		List<Map<Object, Object>> list = getMap(sql);
		writer((String)(((LinkedHashMap<String, Object>)properties.get("excel")).get("path")),
				(String)(((LinkedHashMap<String, Object>)properties.get("excel")).get("fileName")),
				(String)(((LinkedHashMap<String, Object>)properties.get("excel")).get("fileType")),
				list,title,(List<Integer>)(((LinkedHashMap<String, Object>)properties.get("excel")).get("columnWidth")));
    }
    
}